﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using HRM.DAL.DAO;

namespace HRM.DAL.Gateway
{
    public class PaySlipGateway:DBGateway
    {
        public bool IsPaySlipAlreadyCreated(int empid,PaySlip paySlip)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format(@"select * from tblPaySlip where EmpId=@emp and Month=@month and Year=@year");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@emp", SqlDbType.Int).Value = empid;
                CommandObj.Parameters.Add("@month", SqlDbType.VarChar).Value = paySlip.Month;
                CommandObj.Parameters.Add("@year", SqlDbType.VarChar).Value = paySlip.Year;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    return true;
                }

            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Checking Pay Slip Information");
            }
            finally
            {
               ConnectionObj.Close();
            }
            return false;
        }

        public bool SavePaySlip(int empid, PaySlip paySlip)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format(@"insert into tblPaySlip values(@emp,@allowance,@incentive,@deduction,@pf,@netpay,@month,@year,@create,@date)");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@emp", SqlDbType.Int).Value = empid;
                CommandObj.Parameters.Add("@allowance", SqlDbType.Decimal).Value = paySlip.Allowance;
                CommandObj.Parameters.Add("@incentive", SqlDbType.Decimal).Value = paySlip.Incentive;
                CommandObj.Parameters.Add("@deduction", SqlDbType.Decimal).Value = paySlip.Deduction;
                CommandObj.Parameters.Add("@pf", SqlDbType.Decimal).Value = paySlip.PF;
                CommandObj.Parameters.Add("@netpay", SqlDbType.Decimal).Value = paySlip.NetPay;
                CommandObj.Parameters.Add("@month", SqlDbType.VarChar).Value = paySlip.Month;
                CommandObj.Parameters.Add("@year", SqlDbType.VarChar).Value = paySlip.Year;
                CommandObj.Parameters.Add("@create", SqlDbType.VarChar).Value = paySlip.CreateBy;
                CommandObj.Parameters.Add("@date", SqlDbType.DateTime).Value = paySlip.Date;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Creating Pay Slip");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public PaySlip GetPaySlip(int empid, PaySlip paySlip)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format(@"select * from tblPaySlip where EmpId=@emp and Month=@month and Year=@year");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@emp", SqlDbType.Int).Value = empid;
                CommandObj.Parameters.Add("@month", SqlDbType.VarChar).Value = paySlip.Month;
                CommandObj.Parameters.Add("@year", SqlDbType.VarChar).Value = paySlip.Year;
                SqlDataReader reader = CommandObj.ExecuteReader();
                PaySlip pay=new PaySlip();
                while (reader.Read())
                {
                    pay.Id = Convert.ToInt32(reader[0]);
                    Employee employee=new Employee();
                    employee.Id=Convert.ToInt32(reader[1]);
                    pay.Employee = employee;
                    pay.Allowance = Convert.ToDecimal(reader[2]);
                    pay.Incentive = Convert.ToDecimal(reader[3]);
                    pay.Deduction = Convert.ToDecimal(reader[4]);
                    pay.PF = Convert.ToDecimal(reader[5]);
                    pay.NetPay = Convert.ToDecimal(reader[6]);
                    pay.Month = reader[7].ToString();
                    pay.Year = reader[8].ToString();
                    pay.CreateBy = reader[9].ToString();
                    DateTime dt =Convert.ToDateTime(reader[10]);
                    pay.Date = dt.ToShortDateString();
                }
                return pay;

            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Reading Pay Slip Information");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public List<PaySlip> GetAllPaySlip(PaySlip paySlip)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format(@"select * from tblPaySlip where Month=@month and Year=@year");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@month", SqlDbType.VarChar).Value = paySlip.Month;
                CommandObj.Parameters.Add("@year", SqlDbType.VarChar).Value = paySlip.Year;
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<PaySlip> paySlips = new List<PaySlip>();
                while (reader.Read())
                {
                    PaySlip pay = new PaySlip();
                    pay.Id = Convert.ToInt32(reader[0]);
                    Employee employee = new Employee();
                    employee.Id = Convert.ToInt32(reader[1]);
                    pay.Employee = employee;
                    pay.Allowance = Convert.ToDecimal(reader[2]);
                    pay.Incentive = Convert.ToDecimal(reader[3]);
                    pay.Deduction = Convert.ToDecimal(reader[4]);
                    pay.PF = Convert.ToDecimal(reader[5]);
                    pay.NetPay = Convert.ToDecimal(reader[6]);
                    pay.Month = reader[7].ToString();
                    pay.Year = reader[8].ToString();
                    pay.CreateBy = reader[9].ToString();
                    DateTime dt = Convert.ToDateTime(reader[10]);
                    pay.Date = dt.ToShortDateString();
                    paySlips.Add(pay);
                }
                return paySlips;

            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Reading Pay Slip Information");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool EditPaySlip(int empid,PaySlip paySlip)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format(@"update tblPaySlip set EmpId=@emp,Allowance=@allowance,Incentive=@incentive,
                                        Deduction=@deduction,PF=@pf,Netpay=@netpay,Month=@month,Year=@year,CreateBy=@create,Date=@date where Id='"+paySlip.Id+"'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@emp", SqlDbType.Int).Value = empid;
                CommandObj.Parameters.Add("@allowance", SqlDbType.Decimal).Value = paySlip.Allowance;
                CommandObj.Parameters.Add("@incentive", SqlDbType.Decimal).Value = paySlip.Incentive;
                CommandObj.Parameters.Add("@deduction", SqlDbType.Decimal).Value = paySlip.Deduction;
                CommandObj.Parameters.Add("@pf", SqlDbType.Decimal).Value = paySlip.PF;
                CommandObj.Parameters.Add("@netpay", SqlDbType.Decimal).Value = paySlip.NetPay;
                CommandObj.Parameters.Add("@month", SqlDbType.VarChar).Value = paySlip.Month;
                CommandObj.Parameters.Add("@year", SqlDbType.VarChar).Value = paySlip.Year;
                CommandObj.Parameters.Add("@create", SqlDbType.VarChar).Value = paySlip.CreateBy;
                CommandObj.Parameters.Add("@date", SqlDbType.DateTime).Value = paySlip.Date;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Editing Pay Slip");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }
    }
}